import json
import random
import datetime
from django.http import HttpResponse
from app.models import *
from tools.emailcode_send import send
from tools.get_now_time import get_now_time
from tools.get_all import *
from tools.updateInformation import *
from tools.addInformation import *
from tools.deleteinformation import *
from tools.log import log
# 生成报表
import xlwt
import os
from pathlib import Path
from openpyxl.styles import Alignment  # 设置单元格文字对齐方式、自动换行
from django.utils.encoding import escape_uri_path
from tools.sendfile import sendfile
pass_salt = "username_salt"


def login(request):
    """
        键值名称：result
        返回值：
        "密码错误！"：            0
        "登录成功！"：            1
        "未查找到账号！"：         2
        "数据库访问发生错误！"：    3
    """
    if request.method == "POST":
        # 接受用户登录的账号和密码
        req = json.loads(request.body)
        account = req.get('account', None)
        password = req.get('password', None)
        # 创建返回字典
        context = dict()
        # 处理请求（登录验证）
        try:
            # 调用函数实现按照员工账号ID进行查询检索
            obj = User.objects.get(email=account)
            # 判断密码是否正确
            if obj.password != password:
                # 密码错误
                context['result'] = 0
            else:
                # 登录成功
                context['result'] = 1
                res = HttpResponse(json.dumps(context))
                res.set_signed_cookie("account", account, pass_salt)
                right_level = int(obj.right_level)
                if (right_level != 0 and right_level != 1):
                    res.set_cookie('right_level',2)
                else:
                    res.set_cookie('right_level',right_level)
                #写入log日志
                type = "登录"
                time = get_now_time()
                affair = account+"登录了系统"
                log(type, time, affair)

                return res
        except User.DoesNotExist:
            # 未查找到账号
            context['result'] = 2
        except:
            context['result'] = 3
        return HttpResponse(json.dumps(context))
    else:
        return HttpResponse("error")


def login_veri(request):
    """
    验证码登录
    """
    if request.method == "POST":
        req = json.loads(request.body)
        account = req.get('account', None)
        confirm_code = req.get('confirm_code', None)

        # 通过数据库检测该账号是否已经存在
        try:
            # 用户已存在
            obj = User.objects.get(email=account)
            # 创建返回字典
            context = dict()
            # 处理请求 （判断验证码是否正确）
            obj1 = code.objects.get(account=account)
            # 验证码正确
            if confirm_code == obj1.confirm_code:
                context['result'] = 1
                res = HttpResponse(json.dumps(context))
                res.set_signed_cookie("account", account, pass_salt)
                right_level = int(obj.right_level)
                if (right_level != 0 and right_level != 1):
                    res.set_cookie('right_level',2)
                else:
                    res.set_cookie('right_level',right_level)
                #写入log日志
                type = "登录"
                time = get_now_time()
                affair = account+"登录了系统"
                log(type, time, affair)
            # 验证码错误
            else:
                context['result'] = 0
                res = HttpResponse(json.dumps(context))
            return res
        except User.DoesNotExist:
            return HttpResponse(json.dumps({'result': 2}))
        except:
            context['result'] = 0
            return HttpResponse(json.dumps(context))

# 注册成功，保存注册账号和密码
def register(request):
    """
        键值名称：result
        返回值：
        "注册账号成功！"：0
        "注册账号失败！"：1
    """
    if request.method == "POST":
        # 接受用户注册的账号和密码
        req = json.loads(request.body)
        account = req.get('account', None)
        password = req.get('password', None)
        # 创建返回字典
        context = dict()
        # 处理请求（注册信息存入数据库）
        try:
            # 将用户账号信息存入数据库中
            User.objects.create(email=account, password=password, position="员工", right_level=2)
            #写入log日志
            type = "注册"
            time = get_now_time()
            affair = account+"注册成功"
            log(type, time, affair)
            context['result'] = 0
        except:
            # 数据库访问发生错误
            context['result'] = 1
        return HttpResponse(json.dumps(context))
    else:
        return HttpResponse("error")

# 注册界面点击下一步，判断验证码是否正确
def register_email(request):
    """
        前端发送account,confirm_code（验证码）
        后端返回：
        result：
        0：验证码错误
        1：验证码正确，进行下一步
        2：用户已存在
    """
    if request.method == "POST":
        # 接受用户注册的账号和接收到的验证码
        req = json.loads(request.body)
        account = req.get('account', None)
        confirm_code = req.get('confirm_code', None)

        # 通过数据库检测该账号是否已经存在
        try:
            # 用户已存在
            obj = User.objects.get(email=account)
            # 删除验证码信息
            code.objects.filter(account=account).delete()
            return HttpResponse(json.dumps({'result': 2}))
        except User.DoesNotExist:
            # 创建返回字典
            context = dict()
            # 处理请求 （判断验证码是否正确）
            obj = code.objects.get(account=account)
            code.objects.filter(account=account).delete()
            # 验证码正确
            if confirm_code == obj.confirm_code:
                context['result'] = 1

            # 验证码错误
            else:
                context['result'] = 0
            response = HttpResponse(json.dumps(context))
            return response
    else:
        return HttpResponse("error")

# 向邮箱发送验证码
def get_confirm_code(request):
    # 用户点击发送验证码获取请求后
    if request.method == "POST":
        # 接收该用户账号（邮件）
        req = json.loads(request.body)
        user_email = req.get('account', None)
        # user_email = request.data['account']
        # 创建结果返回字典
        context = dict()
        try:
            email_content = '%06d' % random.randint(0, 999999)
            email_title = "验证码获取"
            # 将验证码存入code数据库当中
            code.objects.filter(account=user_email).delete()
            code.objects.create(account=user_email, confirm_code=email_content)
            send(user_email, email_title, email_content)
            # 发送成功
            context['result'] = 1
            # 其他情况
        except:
            context['result'] = 0
        return HttpResponse(json.dumps(context))
    else:
        return HttpResponse("error")


# 实现查询全部用户信息的业务处理
def get_all(request):
    # 判断请求方式（POST）
    if request.method == "POST":
        # 获取参数flag，判定请求需要
        req = json.loads(request.body)
        flag = req.get('table_type',None)
        context = dict()
        # 根据参数，执行相应函数
        if flag == "RoleManage":
            # 查询角色全部信息
            context = get_role_all(request)
        elif flag == "EmployeeManage":
            # 查询用户全部信息
            context = get_user_all()
        elif flag == "WarehouseManage":
            # 查询仓库全部信息
            context = get_warehouse_all()
        elif flag == "MaterialManage" : 
            # 查询物料全部信息
            context = get_material_all()
        elif flag == "DeptManage":
            # 查询部门全部信息
            context = get_department_all()
        elif flag == "InWarehouseManage":
            # 查询入库全部信息
            context = get_IB_all()
        elif flag == "OutWarehouseManage":
            # 查询出库全部信息
            context = get_OB_all()
        elif flag == "NoticeManage":
            # 查询全部公告信息
            context = get_Notice_all()
        elif flag == "MaterialTypeManage":
            # 查询全部物料类别信息
            context = get_MaterialType_all()
        else :
            return HttpResponse("error")  
        return HttpResponse(json.dumps(context))
    else:
        return HttpResponse("error")

# 编辑表格数据
def updateInformation(request):
    # 判断请求方式（POST）
    if request.method == "POST":
        # 获取参数flag，判定请求需要
        req = json.loads(request.body)
        flag = req.get('table_type',None)
        context = dict()
        # 根据参数，执行相应函数
        if flag == "RoleManage":
            # 更新角色信息
            context = updateRoleInformation(request)
        elif flag == "EmployeeManage":
            # 更新用户信息
            context = updateUserInformation(request)
        elif flag == "WarehouseManage":
            # 更新仓库信息
            context = updateWarehouseInformation(request)
        elif flag == "MaterialManage" : 
            # 更新物料信息
            context = updateMaterialInformation(request)
        elif flag == "DeptManage":
            # 更新部门信息
            context = updateDepartmentInformation(request)  
        elif flag == "MaterialTypeManage":
            # 更新物料类别信息
            context = updateMaterialTypeInformation(request)
        else :
            return HttpResponse("error")  
        return HttpResponse(json.dumps(context))
    else:
        return HttpResponse("error")



# 新增信息
def addInformation(request):
    # 判断请求方式（POST）
    if request.method == "POST":
        # 获取参数flag，判定请求需要
        req = json.loads(request.body)
        flag = req.get('table_type',None)
        context = dict()
        # 根据参数，执行相应函数
        if flag == "RoleManage":
            # 新增角色信息
            context = addRole(request)
        elif flag == "EmployeeManage":
            # 新增用户信息
            context = addUser(request)
        elif flag == "WarehouseManage":
            # 新增仓库信息
            context = addWarehouse(request)
        elif flag == "MaterialManage" : 
            # 新增物料信息
            context = addMaterial(request)
        elif flag == "DeptManage":
            # 新增部门信息
            context = addDepartment(request)  
        elif flag == "InWarehouseManage":
            # 新增入库信息
            context = addIB(request)
        elif flag == "OutWarehouseManage":
            # 新增出库信息
            context = addOB(request)
        elif flag == "MaterialTypeManage":
            # 更新物料类别信息
            context = addMaterialType(request)
        else :
            return HttpResponse("error")  
        return HttpResponse(json.dumps(context))
    else:
        return HttpResponse("error")


# 删除用户
def deleteInformation(request):
    # 判断请求方式（POST）
    if request.method == "POST":
        # 获取参数flag，判定请求需要
        req = json.loads(request.body)
        flag = req.get('table_type',None)
        context = dict()
        # 根据参数，执行相应函数
        if flag == "RoleManage":
            # 删除角色信息
            context = deleteRole(request)
        elif flag == "EmployeeManage":
            # 删除用户信息
            context = deleteUser(request)
        elif flag == "WarehouseManage":
            # 删除仓库信息
            context = deleteWarehouse(request)
        elif flag == "MaterialManage" : 
            # 删除物料信息
            context = deleteMaterial(request)
        elif flag == "DeptManage":
            # 删除部门信息
            context = deleteDepartment(request)  
        elif flag == "MaterialTypeManage":
            # 更新物料类别信息
            context = deleteMaterialType(request)
        else :
            return HttpResponse("error")  
        return HttpResponse(json.dumps(context))
    else:
        return HttpResponse("error")



def updateUserInfo(request):
    if request.method == "POST":
        try:
            cookie = request.COOKIES.get("account")
            print(cookie)
            cookie=request.get_signed_cookie("account",salt=pass_salt)
            print(cookie)
            data = json.loads(request.body)
            username = data.get("username", None)
            region = data.get("region", None)
            signature = data.get("signature", None)
            if username is not None:
                User.objects.filter(email=cookie).update(username=username)
            if region is not None:
                User.objects.filter(email=cookie).update(region=region)
            if signature is not None:
                User.objects.filter(email=cookie).update(signature=signature)
            return HttpResponse(json.dumps({
                "result": "OK"
            }))
        except:
            return HttpResponse("error")
    else:
        return HttpResponse("error")

def getUserInfo(request):
    if request.method == "POST":
        try:
            cookie=request.get_signed_cookie("account",salt=pass_salt)
            print(cookie)
            data=User.objects.get(email=cookie)
            returnval=data.__dict__
            returnval.pop("_state")
            return HttpResponse(json.dumps(returnval))
        except:
            return HttpResponse("error")
    else:
        return HttpResponse("error")




# 入库申请处理
def IBhandle(request):
    # 判断请求方式（POST）
    if  request.method == "POST":
        # 获取申请数据
        req = json.loads(request.body)
        # 获取数据
        flag = int(req.get('flag',None))
        req = req.get('data',None)
        # 返回数据
        context = dict()
        # 申请通过
        if flag == 1:
            # 审核通过
            # 处理物料数据库
            # 根据物料相关入库信息建立新的物料    
            Data = dict()
            Data['name'] = req.get("material_name",None)
            Data['type'] = req.get("inventory_type",None)
            Data['code'] = req.get("material_code",None)
            Data['number'] = int(req.get("inventory_quantity",None))
            Data['price'] = float(req.get("price",None))
            Data['Warehouse'] = req.get("dest_warehouse",None)
            Data['inventory_number'] = req.get("inventory_number",None)
            Data['in_time'] = get_now_time()
            Data['remark'] = req.get("remark",None)
            Material.objects.create(**Data)

            # 获取价格数据，存入价格数据库中（假设相同物料的价格是一致的）
            try:
                obj = Price.objects.filter(materialName=Data['name'])
            except Price.DoesNotExist:
                Price.objects.create(materialName=Data['name'],price=Data['price'])


            # 写入Notice公告
            data = dict()
            data['date'] = Data['in_time']
            data['materialName'] = req.get("material_name",None)
            data['IBOB'] = "入库"
            data['warehouse'] = req.get("dest_warehouse",None)
            data['number'] = req.get("inventory_quantity",None)
            Notice.objects.create(**data)

            # 根据入库信息对仓库库存，容量进行修改
            # 仓库现有库存
            stock_now =  Warehouse.objects.get(name=data['warehouse']).stock
            # 修改库存
            Warehouse.objects.filter(name=data['warehouse']).update(stock=stock_now+data['number'])

            # 修改IB中的审批状态
            inventory_number = req.get('inventory_number',None)          #获取入库单号
            #根据入库单号修改审核状态
            IB.objects.filter(inventory_number=inventory_number).update(approval_status="批准")
            # 出库申请通过
            context['result'] = 1


            #写入log日志
            cookie = request.get_signed_cookie("account",salt=pass_salt)
            type = "入库"
            time = get_now_time()
            affair = cookie+"批准了单号为"+inventory_number+"的入库申请"
            log(type, time, affair)
        else:
            # 修改IB中的审批状态
            inventory_number = req.get('inventory_number',None)          #获取入库单号
            #根据入库单号修改审核状态
            IB.objects.filter(inventory_number=inventory_number).update(approval_status="驳回")
            context['result'] = 1
        return HttpResponse(json.dumps(context))
    else:
        return HttpResponse("error")


# 出库审核结果处理
def OBhandle(request):
    # 判断请求方式（POST）
    if request.method == "POST":
        # 获取参数flag，判定请求需要
        req = json.loads(request.body)
        flag = int(req.get('flag',None))
        req = req.get('data',None)
        context = dict()
        # flag 判断审核结果
        if flag == 1:
            # 审核通过
            # 处理物料数据库
            # 获取物料相关出库信息
            outBound_quantity = int(req.get('outBound_quantity',None)) #获取出库数量
            material_code = req.get('material_code',None)              #物料编号
            obj = Material.objects.get(code = material_code)           #根据物料编号找到获取对应物料编号
            # 修改物料库存量
            Material.objects.filter(code=material_code).update(number=int(obj.number)-outBound_quantity)
            # 判断物料剩余数量
            if Material.objects.get(code = material_code).number == 0:
                # 物料全部出库，删除物料信息
                Material.objects.filter(code=material_code).delete()

            # 写入通知
            data = dict()
            data['date'] = get_now_time()
            data['materialName'] = req.get('material_name',None)
            data['IBOB'] = "出库"
            data['warehouse'] = req.get('ori_warehouse',None)
            data['number'] = int(req.get('outBound_quantity',None))
            Notice.objects.create(**data)

            # 根据出库信息对仓库库存，容量进行修改
            # 仓库现有库存
            stock_now =  Warehouse.objects.get(name=data['warehouse']).stock
            # 修改库存
            Warehouse.objects.filter(name=data['warehouse']).update(stock=stock_now-data['number'])

            # 改变状态
            outBound_number = req.get('outBound_number',None)          #获取出库单号
            #根据出库单号修改审核状态
            OB.objects.filter(outBound_number=outBound_number).update(approval_status="批准")
            #处理成功
            context['result'] = 1

            #写入log日志
            cookie = request.get_signed_cookie("account",salt=pass_salt)
            type = "出库"
            time = get_now_time()
            affair = cookie+"批准了单号为"+outBound_number+"的出库申请"
            log(type, time, affair)
        else :
            #审核不通过
            #改变审核状态
            outBound_number = req.get('outBound_number',None)          #获取出库单号
            #根据出库单号修改审核状态
            OB.objects.filter(outBound_number=outBound_number).update(approval_status="驳回")
            #处理成功
            context['result'] = 1
        return HttpResponse(json.dumps(context))
    else:
        return HttpResponse("error")

# 初始时间
date_initial = datetime.date(2022,6,1)
# 报表初始化函数
def start(request):
    # 获取今日日期
    date_today = get_now_time()
    # 获取本月第一天用于计算截止
    dateline = date_today.replace(month = 9,day = 1)
    # 获取仓库号
    req = json.loads(request.body)
    warehouse = req.get('warehouse',None)

    # 设定迭代值date_temp，使用date_initial进行初始化
    date_temp = date_initial
    # 前一月日期
    date_prev = date_initial.replace(month=date_initial.month-1)#不合理，但暂时没影响，不处理
    print(date_temp)
    print("-----------------------")
    print(dateline)
    while True:
        Reportlst = Report.objects.filter(year_month=date_temp,warehouse=warehouse)
        print( not Reportlst)
        if Reportlst:
            print(1)
            #说明该仓库本月份已生成报表
            # 保存前一月日期
            date_prev = date_temp
            # 计算下一月日期
            m = date_temp.month+1
            if m == 12:
                month = 12
                year = date_temp.year
            else:
                month =(date_temp.month+1)%12
                year = date_temp.year + int((date_temp.month+1)/12)
            date_prev = date_temp
            date_temp = datetime.date(year,month,1)
        else:
            break
        
    if date_temp == date_initial:
        # 初月的计算存在差异
        # 搜索物料数据库，获取“物料名称”字段全部值

        materialNames = list(Material.objects.values_list('name',flat=True))
        for materialName in materialNames:
            #初始化起始日期前的报表信息
            data = {
                    'year_month':date_prev,
                    'warehouse':warehouse,
                    'materialName':materialName,
                    'initialNumber': 0,
                    'IBNumber': 0,
                    'OBNumber': 0,
                    'finalNumber':0,
                    'initialMoney': 0,
                    'IBMoney': 0,
                    'OBMoney': 0,
                    'finalMoney': 0,
                    'price':10             #建一个新的表格存储物料价格
                }
            Report.objects.create(**data)
    while date_temp < dateline:        
        #说明仍需计算
        #确定计算时间区间
        #开始时间
        date_start = date_temp
        #结束时间
        year = date_temp.year + int((date_temp.month+1)/12)
        month = (date_temp.month+1)%12
        date_end = datetime.date(year,month,1)
        # 搜索物料数据库，获取“物料名称”字段全部值
        materialNames = list(Material.objects.values_list('name',flat=True))
        for materialName in materialNames:
            # 获取该仓库，物料上个月的报表信息
            obj = Report.objects.get(year_month=date_prev,warehouse=warehouse,materialName= materialName)
            # 创建该仓库，物料这个月的基本报表信息
            data = {
                    'year_month':date_temp,
                    'warehouse':warehouse,
                    'materialName':materialName,
                    'initialNumber': obj.finalNumber,
                    'IBNumber': 0,
                    'OBNumber': 0,
                    'finalNumber':0,
                    'initialMoney': obj.finalMoney,
                    'IBMoney': 0,
                    'OBMoney': 0,
                    'finalMoney': 0,
                    'price':10             #建一个新的表格存储物料价格
                }
            Report.objects.create(**data)
        # 在时间区间内进行搜索公告数据库，获取出入库情况
        Noticelst = Notice.objects.filter(date=(date_start,date_end),warehouse=warehouse)
        for temp in Noticelst:
            obj = Report.objects.get(year_month=date_temp,warehouse=warehouse,materialName= temp.materialName)
            # 判断该通知出入库类型
            if temp.IBOB == "入库":
                # 该条通知是入库
                Report.objects.filter(year_month=date_temp,warehouse=warehouse,materialName= temp.materialName).update(IBNumber=obj.IBNumber+temp.number)
            else :
                # 该条通知是出库
                Report.objects.filter(year_month=date_temp,warehouse=warehouse,materialName= temp.materialName).update(OBNumber=obj.OBNumber+temp.number) #obj不显示参数，可能会错
        # 计算其他值
        for materialName in materialNames:
            obj = Report.objects.get(year_month =date_temp,warehouse=warehouse,materialName= materialName)
            data = {
                    'finalNumber':0,
                    'IBMoney': 0,
                    'OBMoney': 0,
                    'finalMoney': 0,
                }
            #计算月末结存数量
            data['finalNumber'] = obj.initialNumber+obj.IBNumber-obj.OBNumber
            #计算本月入库总金额
            data['IBMoney'] = obj.IBNumber*obj.price
            #计算本月出库总金额
            data['OBMoney'] = obj.OBNumber*obj.price
            #计算月末结存总金额
            data['finalMoney'] = data['finalNumber']*obj.price
            #数据更新
            Report.objects.filter(year_month=date_temp,warehouse=warehouse,materialName= materialName).update(**data)
        #本月计算完成，获取下一月份
        date_prev = date_temp
        year = date_temp.year + int((date_temp.month+1)/12)
        month = (date_temp.month+1)%12
        date_temp = datetime.date(year,month,1)
    
    return

# 人员流动报表下载
def PersonnelworkbookDownload(request):
    if request.method == "POST":
        # 获取申请数据
        req = json.loads(request.body)
        # 获取年时间数据
        year = req.get('year',None)
        fileName = str(year) + "入职和离职人员统计.xlsx"
        return sendfile(fileName)
    else:
        return HttpResponse("error")



# 出入库情况报表下载
def workbookDownload(request):

    # 判断请求方式（POST）
    if  request.method == "POST":
        # 获取申请数据
        req = json.loads(request.body)
        # 获取仓库名称
        warehouse = req.get('warehouse',None)
        # 获取时间数据
        year = int(req.get('year',None))
        month = int(req.get('month',None))
        date = datetime.date(year,month,1)
        # 获取数据库当中对应仓库和时间下的物料信息
        fileName = warehouse+"-"+str(year)+"年"+str(month)+"月报表.xls"
        filePath = os.getcwd() + '/app/static/report/' + fileName
        if os.path.exists(filePath):
            #报表已生成，发送文件
            return sendfile(fileName)
        else :
            #报表未生成
            #报表数据生成
            # start(request)
            #创建报表文件的基本格式
            # 创建一个工作簿，并设置编码
            workbook = xlwt.Workbook(encoding='utf-8')

            # 创建一个工作表
            worksheet = workbook.add_sheet("sheet1")

            # 设置报表样式
            alignment = xlwt.Alignment() # Create Alignment
            alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
            alignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
            style = xlwt.XFStyle() # Create Style
            style.alignment = alignment # Add Alignment to Style

            # 写入报表相关参数
            worksheet.write_merge(0,1,0,0,'物料产品名称',style)
            worksheet.write_merge(0,0,1,3,'月初库存',style)
            worksheet.write_merge(0,0,4,6,'本月入库',style)
            worksheet.write_merge(0,0,7,9,'本月出库',style)
            worksheet.write_merge(0,0,10,12,'月末结存',style)
            worksheet.write_merge(0,1,13,13,'备注',style)
            worksheet.write(1,1,'数量',style)
            worksheet.write(1,2,'单价',style)
            worksheet.write(1,3,'金额',style)
            worksheet.write(1,4,'数量',style)
            worksheet.write(1,5,'单价',style)
            worksheet.write(1,6,'金额',style)
            worksheet.write(1,7,'数量',style)
            worksheet.write(1,8,'单价',style)
            worksheet.write(1,9,'金额',style)
            worksheet.write(1,10,'数量',style)
            worksheet.write(1,11,'单价',style)
            worksheet.write(1,12,'金额',style)


            #生成报表
            print(date)
            conditions={
                'warehouse':warehouse,
                'year_month':date
            }
            obj_list = Report.objects.filter(**conditions)
            excel_row = 2
            for obj in obj_list:
                materialName = obj.materialName
                initialNumber = obj.initialNumber
                IBNumber = obj.IBNumber
                OBNumber = obj.OBNumber
                finalNumber = obj.finalNumber
                initialMoney = obj.initialMoney
                IBMoney = obj.IBMoney
                OBMoney = obj.OBMoney
                finalMoney = obj.finalMoney
                unitPrice = obj.price
                worksheet.write(excel_row,0,materialName)
                worksheet.write(excel_row,1,initialNumber)
                worksheet.write(excel_row,2,unitPrice)
                worksheet.write(excel_row,3,initialMoney)
                worksheet.write(excel_row,4,IBNumber)
                worksheet.write(excel_row,5,unitPrice)
                worksheet.write(excel_row,6,IBMoney)
                worksheet.write(excel_row,7,OBNumber)
                worksheet.write(excel_row,8,unitPrice)
                worksheet.write(excel_row,9,OBMoney)
                worksheet.write(excel_row,10,finalNumber)
                worksheet.write(excel_row,11,unitPrice)
                worksheet.write(excel_row,12,finalMoney)
                excel_row += 1

            # 保存报表文件
            workbook.save(filePath)

            # 向前端发送报表文件
            return sendfile(fileName)
    else:
        return HttpResponse("error")

def applyfor(request):
    #申请认证
    '''
    前端发送：account,name,gender,year,department，position，telephone，entry_time(账户以及需要认证的各种信息)
    后端返回：
    result：
    申请成功，等待审核：0
    申请失败：1
    '''
    if request.method == "POST":
        #接收该用户的账号（邮箱）
        req = json.loads(request.body)
        user_email = request.get_signed_cookie("account",salt=pass_salt)
        #接收该用户申请认证的各信息
        user_name = req.get('name', None)
        user_gender = req.get('gender',None)
        user_telephone = req.get('telephone', None)
        # 创建结果返回字典
        try:
            staff=AllReport.objects.get(email=user_email)
            if staff.name == user_name \
                and staff.gender == user_gender \
                and staff.telephone == user_telephone:
                temp = staff.__dict__
                temp.pop("_state")
                User.objects.update_or_create(
                    defaults=temp,
                    email=user_email
                )
                return HttpResponse(json.dumps({"result":0}))
            else:
                return HttpResponse(json.dumps({"result": 1}))
        except AllReport.DoesNotExist:
            return HttpResponse(json.dumps({"result": 1}))
        except:
            return HttpResponse(json.dumps({"result": "error"}))

# 上下班打卡
def punch(request):
    if request.method == "POST":
        # 获取签到打开类型
        req = json.loads(request.body)
        type = req.get('type',None)
        #获取用户邮箱号
        email = request.get_signed_cookie("account",salt=pass_salt)
        # 返回结果
        context = dict()
        if type == "up":
            #上班打卡
            try:
                # 打开成功
                UpPunch.objects.create(email = email,time = get_now_time())
                context = 1
            except:
                # 打卡失败
                context = 0
        else:
            #下班打卡
            try:
                #打卡成功
                DownPunch.objects.create(email = email,time = get_now_time())
                context = 1
            except:
                # 打卡失败
                context = 0
        return HttpResponse(json.dumps(context))

# 查询签到情况
def checkoutpunch(request):
    if request.method == "POST":
        # 获取签到打开类型
        req = json.loads(request.body)
        type = req.get('type',None)
        #获取用户邮箱号
        email = request.get_signed_cookie("account",salt=pass_salt)
        # 返回结果
        context = dict()
        if type == "up":
            #上班打卡检验
            try:
                # 已上班打卡
                UpPunch.objects.get(email=email,time=get_now_time())
                context = 1
            except UpPunch.DoesNotExist:
                # 未上班打卡
                context = 0
            except:
                # 发生未知错误
                context = 2
        else:
            #下班打卡检验
            try:
                # 已下班打卡
                DownPunch.objects.get(email=email,time=get_now_time())
                context = 1
            except DownPunch.DoesNotExist:
                # 未上班打卡
                context = 0
            except:
                # 发生未知错误
                context = 2
        return HttpResponse(json.dumps(context))

